﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Scheduling.Models.Interfaces;
using Scheduling.Models.Helpers;
using Scheduling.Models.Repos.Database.Objects;
using Scheduling.Models.Extensions;

namespace Scheduling.Models.Repos.Database
{
    public class DatabaseUserLoader : IUserLoader
    {
        public List<User> GetUsers()
        {
            var db = DBContext.Create();
            var q = from u in db.Users
                    join ug in db.UserGroups on u.GroupID equals ug.ID
                    join ud in db.Departments on u.DepartmentID equals ud.ID
                    select new { u, ug, ud };
            return LinqHelper.GenericMap<User>(q, "u", "ug>UserGroup", "ud>Department");
        }

        public User GetUser(string UserID)
        {
            var db = DBContext.Create();
            var q = from u in db.Users
                    join ug in db.UserGroups on u.GroupID equals ug.ID
                    join ud in db.Departments on u.DepartmentID equals ud.ID
                    where (u.ID == UserID)
                    select new { u, ug, ud };
            if (q.Count() == 1)
                return LinqHelper.GenericMap<User>(q, "u", "ug>UserGroup", "ud>Department").First();
            else
                return null;
        }

        public bool UserExists(string UserID)
        {
            var db = DBContext.Create();
            return (from u in db.Users where u.ID == UserID select u).Count() > 0;
        }

        public void DeleteUser(string UserID)
        {
            var db = DBContext.Create();
            var q = from u in db.Users where(u.ID == UserID) select u;
            if (q.Count() > 0)
            {
                string query = string.Format("DELETE FROM Users WHERE ID = '{0}'",UserID);
                query.SqlFormat(UserID);
                db.ExecuteCommand(query);
            }
        }

        public void InsertUser(User user)
        {
            var db = DBContext.Create();
            string command=string.Format("INSERT INTO Users(ID, FirstName, LastName, Password, Phone,Email, DepartmentID, GroupID) VALUES('{0}', '{1}', '{2}', '{3}','{4}','{5}',{6},{7})",
                user.ID,user.FirstName,user.LastName,user.Password,user.Phone,user.Email,user.Department.ID,user.UserGroup.ID);
            db.ExecuteCommand(command);
        }

        public void EditUser(User user)
        {
            var db = DBContext.Create();
            db.ExecuteCommand(
                string.Format("UPDATE Users SET FirstName = '{1}', LastName = '{2}', Password = '{3}', Phone = '{4}', Email = '{5}', DepartmentID = {6}, GroupID = {7} WHERE ID = '{0}'",
                user.ID, user.FirstName, user.LastName, user.Password, user.Phone, user.Email, user.Department.ID, user.UserGroup.ID));
        }
        
        public void Clear(){
            throw new NotImplementedException();
        }


    }
}